create or replace function get_id_by_seqname(seq_name in varchar2) RETURN number as
--根据序列名称得到逻辑主键
tab_id number :=0;
begin
  execute immediate 'select '||seq_name||'.nextval from dual' into tab_id;
  return tab_id;
end;


create or replace function get_biz_no(no_flag in number, business_type in varchar2) RETURN varchar2 as
--根据业务类型得到业务编号，如业务流水号、交易订单编号、支付订单编号
/**
  规则：
       由对应seq_name得到八位字符串v_seqid，在该串前后分别加上一位随机数
       v_seqid第2、4、6位后分别加上年、月、日，年月日都为2位
       v_seqid第1、3、5、7位后加上业务类型business_type的第1、2、3、4个字符
**/
tab_id number :=0;
v_seqid varchar2(8);
v_date varchar2(8);
v_bizno varchar2(50);
v_random1 number :=1;
v_random2 number :=9;

begin
  -- 1 交易订单编号  2 支付订单编号
  select decode(no_flag, 1, SEQ_TRADE.Nextval, 2, SEQ_PAYMENT.Nextval, 0) into tab_id from dual;
  
  if tab_id = 0 then
    return null;
  end if;

  select lpad(to_char((100000000-mod(tab_id,100000000))),8,'0') into v_seqid from dual;--八位
  select to_char(sysdate, 'yyyymmdd') into v_date from dual;
  select round(dbms_random.value(1,9)) into v_random1 from dual;
  select round(dbms_random.value(1,9)) into v_random2 from dual;
  select v_date || v_random1||substr(v_seqid,1,1)||substr(business_type,1,1)||substr(v_seqid,2,1)||substr(v_date,3,2)||substr(v_seqid,3,1)||substr(business_type,2,1)
         ||substr(v_seqid,4,1)||substr(v_date,5,2)||substr(v_seqid,5,1)||substr(business_type,3,1)||substr(v_seqid,6,1)||substr(v_date,7,2)||substr(v_seqid,7,1)
         ||substr(business_type,4,1)||substr(v_seqid,8,1)||v_random2 into v_bizno from dual;
  return v_bizno;
end;




